package utils;

import java.sql.*;
import java.util.Map;

/**
 * @version V1.0
 * @Title:
 * @Package
 * @Description:
 * @author: runoob
 * @date:
 */
public class DataManageUtils {

    static public Connection getConnection() throws ClassNotFoundException, SQLException {
        String url = "jdbc:h2:./database;MV_STORE=FALSE;MVCC=FALSE;TRACE_LEVEL_FILE=0;TRACE_LEVEL_SYSTEM_OUT=0";
        String driverClass = "org.h2.Driver";
        Class.forName(driverClass);
        return DriverManager.getConnection(url);
    }

    static public void recreateTable(Statement statement) throws SQLException {
        statement.execute("CREATE TABLE IF NOT EXISTS recordsTMP(" +
                "autoid int(11) auto_increment," +
                "id varchar(5) primary key," +
                "title varchar(100)," +
                "url varchar(1000)," +
                "method varchar(5)," +
                "isEncrypt varchar(1)," +
                "dataSecret varchar(1000)," +
                "dataSecretIV varchar(1000)," +
                "appSecret varchar(1000)," +
                "body varchar(1000000)," +
                "result varchar(1000000))"
        );
        statement.executeUpdate("insert into recordsTMP select * from records");
        statement.execute("DROP TABLE IF EXISTS records");
        statement.execute("CREATE TABLE IF NOT EXISTS records(" +
                "autoid int(11) auto_increment," +
                "id varchar(5) primary key," +
                "title varchar(100)," +
                "url varchar(1000)," +
                "method varchar(5)," +
                "isEncrypt varchar(1)," +
                "dataSecret varchar(1000)," +
                "dataSecretIV varchar(1000)," +
                "appSecret varchar(1000)," +
                "body varchar(1000000)," +
                "result varchar(1000000))"
        );
        statement.executeUpdate("insert into records select * from recordsTMP");
        statement.execute("DROP TABLE IF EXISTS recordsTMP");
    }

    static public void createTable(Statement statement) throws SQLException {
//        statement.execute("DROP TABLE IF EXISTS records");
//        statement.execute("DROP TABLE IF EXISTS params");
//        statement.execute("DROP TABLE IF EXISTS headers");
//        statement.execute("DROP TABLE IF EXISTS template");
        statement.execute("CREATE TABLE IF NOT EXISTS records(" +
                "autoid int(11) auto_increment," +
                "id varchar(5) primary key," +
                "title varchar(100)," +
                "url varchar(1000)," +
                "method varchar(5)," +
                "isEncrypt varchar(1)," +
                "dataSecret varchar(1000)," +
                "dataSecretIV varchar(1000)," +
                "appSecret varchar(1000)," +
                "body varchar(100000)," +
                "result varchar(1000000))"
        );

        statement.execute("CREATE TABLE IF NOT EXISTS params(" +
                "id varchar(5)," +
                "paramkey varchar(1000)," +
                "paramvalue varchar(5000)," +
                "paramdescription varchar(5000))"
        );

        statement.execute("CREATE TABLE IF NOT EXISTS headers(" +
                "id varchar(5)," +
                "headerkey varchar(1000)," +
                "headervalue varchar(5000)," +
                "headerdescription varchar(5000))"
        );

        statement.execute("CREATE TABLE IF NOT EXISTS template(" +
                "id varchar(5)," +
                "templatekey varchar(1000)," +
                "templatevalue varchar(5000)," +
                "templatedescription varchar(5000))"
        );
    }

    static public void insertRecord(Statement statement, Map<String,Object> valueMap) throws SQLException {
        // 添加数据
        statement.executeUpdate("insert into records(id,title,url,method,isEncrypt,dataSecret,dataSecretIV,appSecret,body,result) values(" +
                "'" + valueMap.get("id") + "'," +
                "'" + valueMap.get("title") + "'," +
                "'" + valueMap.get("url") + "'," +
                "'" + valueMap.get("method") + "'," +
                "'" + valueMap.get("isEncrypt") + "'," +
                "'" + valueMap.get("dataSecret") + "'," +
                "'" + valueMap.get("dataSecretIV") + "'," +
                "'" + valueMap.get("appSecret") + "'," +
                "'" + valueMap.get("body") + "'," +
                "'" + valueMap.get("result") + "')");
    }

    static public void updateRecord(Statement statement, Map<String,Object> valueMap) throws SQLException {
        String sql = "update records set " +
                "title = '" + valueMap.get("title") + "'," +
                "url = '" + valueMap.get("url") + "'," +
                "method = '" + valueMap.get("method") + "'," +
                "isEncrypt = '" + valueMap.get("isEncrypt") + "'," +
                "dataSecret = '" + valueMap.get("dataSecret") + "'," +
                "dataSecretIV = '" + valueMap.get("dataSecretIV") + "'," +
                "appSecret = '" + valueMap.get("appSecret") + "'," +
                "body = '" + valueMap.get("body") + "'," +
                "result = '" + valueMap.get("result") + "' " +
                "where id = '" + valueMap.get("id") + "'";
//        System.out.println(sql);
        // 添加数据
        statement.executeUpdate(sql);
    }

    static public void insertParam(Statement statement, Map<String,Object> valueMap) throws SQLException {
        // 添加数据
        statement.executeUpdate("insert into params(id,paramkey,paramvalue,paramdescription) values(" +
                "'" + valueMap.get("id") + "'," +
                "'" + valueMap.get("paramkey") + "'," +
                "'" + valueMap.get("paramvalue") + "'," +
                "'" + valueMap.get("paramdescription") + "')");
    }

    static public void insertHeader(Statement statement, Map<String,Object> valueMap) throws SQLException {
        // 添加数据
        statement.executeUpdate("insert into headers(id,headerkey,headervalue,headerdescription) values(" +
                "'" + valueMap.get("id") + "'," +
                "'" + valueMap.get("headerkey") + "'," +
                "'" + valueMap.get("headervalue") + "'," +
                "'" + valueMap.get("headerdescription") + "')");
    }

    static public void insertTemplate(Statement statement, Map<String,Object> valueMap) throws SQLException {
        String sql = "insert into template(id,templatekey,templatevalue,templatedescription) values(" +
                "'" + valueMap.get("id") + "'," +
                "'" + valueMap.get("templatekey") + "'," +
                "'" + valueMap.get("templatevalue") + "'," +
                "'" + valueMap.get("templatedescription") + "')";
//        System.out.println("insertTemplate: " + sql);
        // 添加数据
        statement.executeUpdate(sql);
    }

    static public void updateRecordTitle(Statement statement, Map<String,Object> valueMap) throws SQLException {
        statement.executeUpdate("update records set title = '" + valueMap.get("title") + "' where id = '" + valueMap.get("id") + "'");
    }

    static public ResultSet getAllRecords(Statement statement, Map<String,Object> valueMap) throws SQLException {
        String sql = "select * from records";
        if (!valueMap.get("data").equals("")){
            sql = sql + " WHERE title like '%" + valueMap.get("data") + "%' OR url like '%" + valueMap.get("data") + "%'";
        }
        sql += " order by autoid";
        return statement.executeQuery(sql);
    }

    static public void deleteAllRecords(Statement statement) throws SQLException {
        statement.executeUpdate("delete from records");
        statement.executeUpdate("delete from params");
        statement.executeUpdate("delete from headers");
        statement.executeUpdate("delete from template");
    }

    static public void deleteRecord(Statement statement, Map<String,Object> valueMap) throws SQLException {
        statement.executeUpdate("delete from records where id = '" + valueMap.get("id") + "'");
        statement.executeUpdate("delete from params where id = '" + valueMap.get("id") + "'");
        statement.executeUpdate("delete from headers where id = '" + valueMap.get("id") + "'");
        statement.executeUpdate("delete from template where id = '" + valueMap.get("id") + "'");
    }

    static public void deleteParam(Statement statement, Map<String,Object> valueMap) throws SQLException {
        statement.executeUpdate("delete from params where id = '" + valueMap.get("id") + "'");
    }

    static public void deleteHeader(Statement statement, Map<String,Object> valueMap) throws SQLException {
        statement.executeUpdate("delete from headers where id = '" + valueMap.get("id") + "'");
    }

    static public void deleteTemplate(Statement statement, Map<String,Object> valueMap) throws SQLException {
        String sql = "delete from template where id = '" + valueMap.get("id") + "'";
        System.out.println("deleteTemplate: " + sql);
        statement.executeUpdate(sql);
    }

    static public ResultSet getRecord(Statement statement, Map<String,String> valueMap) throws SQLException {
        String sql = "select * from records where 1=1 ";
        if (valueMap.containsKey("title")){
            sql += "and title like '%" + valueMap.get("protocol") + "%'";
        }
        sql += " order by autoid";
        return statement.executeQuery(sql);
    }

    static public ResultSet getParam(Statement statement, Map<String,Integer> valueMap) throws SQLException {
        String sql = "select * from params where 1=1 ";
        if (valueMap.containsKey("id")){
            sql += "and id = '" + valueMap.get("id") + "'";
        }
        return statement.executeQuery(sql);
    }

    static public ResultSet getHeader(Statement statement, Map<String,Integer> valueMap) throws SQLException {
        String sql = "select * from headers where 1=1 ";
        if (valueMap.containsKey("id")){
            sql += "and id = '" + valueMap.get("id") + "'";
        }
        return statement.executeQuery(sql);
    }

    static public ResultSet getTemplate(Statement statement, Map<String,Integer> valueMap) throws SQLException {
        String sql = "select * from template where 1=1 ";
        if (valueMap.containsKey("id")){
            sql += "and id = '" + valueMap.get("id") + "'";
        }
//        System.out.println("getTemplate: " + sql);
        return statement.executeQuery(sql);
    }

}
